package com.icss.daos;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import com.icss.beans.NoticeVO;
import com.icss.utils.DB;

/**
 * @author 廖鹏
 * @version 创建时间：2010-3-16 下午05:20:30
 * 类说明
 */
public class NoticeDao {
	
	private Dao dao=null;
	private ResultSet rSet=null;
	
	public NoticeDao(){
	}
	
	/**
	 * @author 廖鹏
	 * 功能：添加公告
	 * @param noticeVO
	 * @return
	 */
	@SuppressWarnings("deprecation")
	public  boolean addNotice(NoticeVO noticeVO){
		dao=new Dao();
		StringBuffer sqlBuffer=new StringBuffer();
		sqlBuffer.append("insert into notice(noticeTitle,noticeContent,noticeTime) values(");
		sqlBuffer.append("'").append(noticeVO.getNoticeTitle()).append("','")
			.append(noticeVO.getNoticeContent()).append("','").append(noticeVO.getNoticeTime().toLocaleString()).append("')");
		boolean b=dao.execute(sqlBuffer.toString());
		dao.close();
		return b;
	}
	
	/**
	 * @author 廖鹏
	 * 功能：删除一条公告
	 * @param noticeID
	 * @return
	 */
	public boolean delNotice(int noticeID){
		dao=new Dao();
		StringBuffer sqlBuffer=new StringBuffer();
		sqlBuffer.append("delete from notice where noticeID=").append(noticeID);
		boolean b=dao.execute(sqlBuffer.toString());
		dao.close();
		return b;
	}
	
	/**
	 * @author 廖鹏
	 * 功能：批量删除
	 * @param ar
	 * @return
	 */
	public boolean delNotice(int[] ar){
		Connection connection=DB.getConnection();
		int i=0;
		
		try {
			connection.setAutoCommit(false);
			PreparedStatement preparedStatement=connection.prepareStatement("delete from notice where noticeID=?");
			while(i<ar.length){
				preparedStatement.setInt(1, ar[i]);
				preparedStatement.executeUpdate();
				i++;				
			}
			if(i==ar.length){
				connection.commit();
				return true;
			}else {
				connection.rollback();
				return false;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		return false;
	}
	
	/**
	 * @author 廖鹏
	 * 功能：最近五条公告
	 * @return
	 */
	public List<NoticeVO> getRecently(){
		dao=new Dao();
		StringBuffer sqlBuffer=new StringBuffer();
		sqlBuffer.append("select * from notice order by noticeTime desc limit 0,5");
		rSet=dao.query(sqlBuffer.toString());
		List<NoticeVO> notiList=new ArrayList<NoticeVO>();
		try {
			while(rSet.next()){
				NoticeVO noticeVO=new NoticeVO();
				noticeVO.setNoticeID(rSet.getInt("noticeID"));
				noticeVO.setNoticeTitle(rSet.getString("noticeTitle"));
				noticeVO.setNoticeContent(rSet.getString("noticeContent"));
				noticeVO.setNoticeTime(rSet.getDate("noticeTime"));
				notiList.add(noticeVO);
			}
			return notiList;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}
	
	/**
	 * @author 廖鹏
	 * 功能：根据条件获取分页结果集
	 * @param noticeTime 查询的时间
	 * @param selectType 时间类型：年、月、日
	 * @param currentPage 当前页
	 * @param pageSize 每页多少条记录
	 * @return
	 */
	public List<NoticeVO> getPageNotices(String noticeTime, String selectType,int currentPage,int pageSize){
		StringBuffer sqlBuffer=new StringBuffer();
		
		if("".equals(selectType)||"day".equals(selectType)){
			sqlBuffer.append("noticeTime='").append(noticeTime).append("'");
		}
		else if("month".equals(selectType)){
			sqlBuffer.append("month(noticeTime)=month('").append(noticeTime).append("')")
				.append("and year(noticeTime)=year('").append(noticeTime).append("')");
		}else{
			sqlBuffer.append("year(noticeTime)=year('").append(noticeTime).append("')");			
		}
		Connection connection=DB.getConnection();
		List<NoticeVO> noticList=new ArrayList<NoticeVO>();
		NoticeVO noticeVO=null;
		try {
			CallableStatement callableStatement=connection.prepareCall("{call proc_pageable(?,?,?,?,?,?,?,?,?)}");
			callableStatement.setString(3, "notice");
			callableStatement.setString(4, "*");
			callableStatement.setString(5, "noticeTime");
			callableStatement.setString(6, sqlBuffer.toString());
			callableStatement.setInt(7, pageSize);
			callableStatement.setInt(8, currentPage);
			callableStatement.setInt(9, 0);
			callableStatement.registerOutParameter(1, java.sql.Types.INTEGER);
			callableStatement.registerOutParameter(2, java.sql.Types.INTEGER);
			ResultSet rSet=callableStatement.executeQuery();
			while(rSet.next()){
				noticeVO=new NoticeVO();
				noticeVO.setNoticeID(rSet.getInt("noticeID"));
				noticeVO.setNoticeTitle(rSet.getString("noticeTitle"));
				noticeVO.setNoticeContent(rSet.getString("noticeContent"));
				noticeVO.setNoticeTime(new Date(rSet.getDate("noticeTime").getTime()));
				noticList.add(noticeVO);
			}
			return noticList;
		} catch (SQLException e1) {
			e1.printStackTrace();
		}finally{
			try {
				connection.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return noticList;
	}
	
	/**
	 * @author 廖鹏
	 * 功能：查询记录总数
	 * @param startTime
	 * @param selectType
	 * @return
	 */
	public int selectNoticeTotal(String startTime, String selectType) {
		dao=new Dao();
		StringBuffer sqlBuffer = new StringBuffer();

		if ("".equals(selectType) || "day".equals(selectType)) {
			sqlBuffer.append("select count(*) as totalRecord from Notice where noticeTime='").append(startTime).append("'");
		} else if ("month".equals(selectType)) {
			sqlBuffer.append("select count(*) as totalRecord from Notice where month(noticeTime)=month('").append(startTime).append("')")
					.append("and year(noticeTime)=year('").append(startTime).append("')");
		} else {
			sqlBuffer.append("select count(*) as totalRecord from Notice where year(noticeTime)=year('").append(startTime).append("')");
		}

		rSet = dao.query(sqlBuffer.toString());
		int totalRecord=0;
		try {			
			while(rSet.next()){
				totalRecord=rSet.getInt("totalRecord");				
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			dao.close();
		}
		return totalRecord;
	}
	/**
	 * 功能：获取一条公告信息
	 * @param noticeID
	 * @return
	 */
	public NoticeVO getNotice(int noticeID){
		String sql = "select * from notice where noticeID="+noticeID;
		dao= new Dao();
		rSet=dao.query(sql);
		NoticeVO noticeVO=new NoticeVO();
		try {
			while(rSet.next()){
				noticeVO.setNoticeID(rSet.getInt("noticeID"));
				noticeVO.setNoticeTitle(rSet.getString("noticeTitle"));
				noticeVO.setNoticeContent(rSet.getString("noticeContent"));
				noticeVO.setNoticeTime(new Date(rSet.getDate("noticeTime").getTime()));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return noticeVO;
	}
	
	/**
	 * 功能：更新一条公告
	 * @param noticeVO
	 * @return
	 */
	public boolean updateNotice(NoticeVO noticeVO){		
		return false;
	}

}
